package com.google.code.p.localspinner.database;
import com.google.code.p.localspinner.model.Item;
import com.google.code.p.localspinner.model.ItemList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class UserdataAdapter{
    public static final String COLUMN_ID = "_id";
    public static final String COLUMN_NAME = "list_name";
    private static final String COLUMN_LIST_ID = "list_id";
    private static final String COLUMN_TITLE = "title";
    private static final String COLUMN_BODY = "body";
    
    private static final String TABLE_LIST = "list";
    private static final String TABLE_LIST_ITEMS = "list_items";
    
    private DatabaseHelper dbHelper;
    private SQLiteDatabase db;
    
    /**
     * Database creation sql statement
     */
    private static final String CREATE_LIST_TABLE =
            "create table "+ TABLE_LIST +" ("+COLUMN_ID+
            " integer primary key autoincrement, "+COLUMN_NAME+" text not null);";
    private static final String CREATE_LIST_ITEMS_TABLE =
      "create table "+ TABLE_LIST_ITEMS +
      " ("+
        COLUMN_ID+" integer primary key autoincrement, " +
        COLUMN_LIST_ID +" integer not null, "+
        COLUMN_TITLE +" text not null, "+
        COLUMN_BODY+" text not null " +
        "constraint "+ COLUMN_LIST_ID + " references " +
        TABLE_LIST + "(" + COLUMN_ID + ") on delete cascade" +
      ");";    

    private static final String DATABASE_NAME = "com_google_code_localspinner";
    private static final int DATABASE_VERSION = 2;

    private final Context ctx;

    public UserdataAdapter(Context ctx){
        this.ctx = ctx;
    }
    
    /**
     * get all lists
     * @return a cursor of {_id, list_name}
     */
    public Cursor fetchAllLists() {
      return db.query(TABLE_LIST, new String[]{COLUMN_ID, COLUMN_NAME}, null, null, null, null, null);
    }
    
    /**
     * Delete this a list from the db
     * @param rowId
     * @return
     */
    public boolean deleteList(long rowId){
      return db.delete(TABLE_LIST, COLUMN_ID + "=" + rowId, null) > 0;
    }
    
    /**
     * Retrieve an itemList from the database
     * @param rowId
     * @return
     */
    public ItemList getItemList(Long rowId){
      Cursor list = db.query(TABLE_LIST, new String[]{COLUMN_NAME}, COLUMN_ID + "="+rowId, null, null, null, null);
      if(!list.moveToFirst()){
        throw new SQLException("Unable to retrieve list");
      }
      String name = list.getString(0);
      Cursor items = db.query(TABLE_LIST_ITEMS, new String[]{COLUMN_TITLE, COLUMN_BODY}, COLUMN_LIST_ID+ "=" +rowId,
          null, null, null, null);
      ItemList ret = new ItemList();
      ret.setListName(name);
      while(items.moveToNext()){
        ret.addItem(new Item(items.getString(0), items.getString(1)));
      }
      if(ret.size() < 1){
        throw new SQLException("Unable to retrieve list");
      }
      return ret;
    }
    
    /**
     * Save the input list to database
     * @param itemlist
     * @throws SQLException
     */
    public void saveItemList(ItemList itemlist) throws SQLException{
      saveList(itemlist, db);
    }
    
    /**
     * Open the notes database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public UserdataAdapter open() throws SQLException {
        dbHelper = new DatabaseHelper(ctx);
        db = dbHelper.getWritableDatabase();
        return this;
    }
    
    public void close() {
        if(dbHelper != null){
            dbHelper.close();
        }
    }
    
    private static void saveList(ItemList itemlist, SQLiteDatabase db) throws SQLException{
        ContentValues list = new ContentValues();
        list.put(COLUMN_NAME, itemlist.getListName());
        long id = db.insertOrThrow(TABLE_LIST, null, list);
        for(Item item : itemlist.getItems()){
          ContentValues row = new ContentValues();
          row.put(COLUMN_TITLE, item.getTitle());
          row.put(COLUMN_BODY, item.getBody());
          row.put(COLUMN_LIST_ID, id);
          db.insertOrThrow(TABLE_LIST_ITEMS, null, row);
        }  
    }
    
    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(CREATE_LIST_TABLE);
            db.execSQL(CREATE_LIST_ITEMS_TABLE);
            try {
                ItemList list = new ItemList();
                list.setListName("Wheel of Decision");
                list.addItem(new Item("YES!", "Yeeeeeeeeeess! Go for it!!"));
                list.addItem(new Item("NO!", "Noooooooooo! Don't do it!"));
                saveList(list, db);
                list = new ItemList();
                list.setListName("Wheel of Judgement");
                list.addItem(new Item("GOOD!", "Excellent!! It's the best thing ever!"));
                list.addItem(new Item("BAD!", "Booooo!! It's the worst thing ever!"));
                list.addItem(new Item("MEDIOCRE!", "Yaaaaawnn... Whatever..."));
                saveList(list, db);
            } catch (Exception e) {
                //doesnt matter
            }
            
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
          Log.w(UserdataAdapter.class.toString(), "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS "+TABLE_LIST);
            db.execSQL("DROP TABLE IF EXISTS "+TABLE_LIST_ITEMS);
            onCreate(db);
        }
    }
}